package CloudStorage;

import java.util.ArrayList;
import java.sql.*;
import CloudStorageExceptions.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class Database
{

    private Connection connection;
    private Statement statement;

    /**
     * Initializes the database connection
     * 
     * @param url to database
     *
     * @return new database instance connected to the specified url
     */

    public Database() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException
    {
        String userName = "root";
        String password = "10011988";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/cloudstoragedb", userName, password);
        statement = connection.createStatement();
    }

    /**
     * Inserts new user in the connected database
     *
     * @param newUser User to be inserted
     * @throws SQLException
     * @throws UsernameExistsException
     * @throws EmailExistsException
     */
    
    public void insertUser(User newUser) throws SQLException, UsernameExistsException, EmailExistsException
    {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = new java.util.Date();
        String registrationDate = dateFormat.format(date);

        try {
            String insert = "INSERT INTO Users ( `Username`, `Password`, `Email`, `Surname`, `Name`, `AvailableSpace`, `RegistrationDate`, `LastLogin` ) ";
            insert += "VALUES ";
            insert += "('" + newUser.getUsername() + "', '" + newUser.getPassword() + "', '" + newUser.getEmail() + "', '"
                    + newUser.getSurname() + "', '" + newUser.getName() + "', 0 , '" + newUser.getRegistrationDate() + "','" + registrationDate + "')";
            statement.executeUpdate(insert);
        } catch (Exception exception) {
            if (exception.getMessage().toString().indexOf("Username_UNIQUE") != -1) {
                throw new UsernameExistsException();
            } else if (exception.getMessage().toString().indexOf("Email") != -1) {
                throw new EmailExistsException();
            }
        }

        int userId;

        String select = "SELECT id FROM Users WHERE Username = '" + newUser.getUsername() + "';";
        ResultSet resultSet = statement.executeQuery(select);
        resultSet.next();
        userId = resultSet.getInt("id");
        String insert = "INSERT INTO Carts (`User`) VALUES (" + String.valueOf(userId) + ")";
        statement.executeUpdate(insert);
    }

    /**
     * Inserts new file in the connected database
     *
     * @param newFile file to be inserted
     * @throws SQLException
     */

    public void insertFile(File newFile, String keywords) throws SQLException
    {
        String insert = "INSERT INTO Files ( `Owner`, `Name`, `Status`, `Value`, `Downloads`, `Category`, `Description`, `DatePublished` )";
        insert += "VALUES ";
        insert += "( " + newFile.getOwnerId() + ", '" + newFile.getFullName() + "', '" + newFile.getStatus() + "', " + newFile.getValue()
                + ", " + newFile.getDownloads() + ", '" + newFile.getCategory() + "' ,'" + newFile.getDescription() + "', '" + newFile.getDatePublished() + "');";

        statement.executeUpdate(insert);

        /* Get file id from database */

        String select = "SELECT id FROM Files WHERE Name = '" + newFile.getFullName() + "' AND Owner = " + String.valueOf(newFile.getOwnerId());
        ResultSet resultSet = statement.executeQuery(select);
        resultSet.next();
        int fileId = resultSet.getInt("id");

        /* Insert Keywords */

        String[] keywordsArray;
        String delimiter = ",";
        keywords = keywords.trim();
        keywords += "," + Utility.getPostFix(newFile.getName()) + "," + newFile.getCategory();
        keywords += "," + Utility.getPrefix(newFile.getName());
        keywords += "," + this.getUsername( newFile.getOwnerId()).toString();
        keywordsArray = keywords.split(delimiter);
        
        for ( int i = 0; i < keywordsArray.length; i++ )
        {
            insert = "INSERT INTO Keywords (`Files_id`, `Keyword`) VALUES ( " + fileId + ", '" + keywordsArray[i] + "');";
            statement.executeUpdate(insert);
        }
    }

    /**
     * Inserts a new cart in the connected database
     *
     * @param newCart cart to be inserted
     * @throws SQLException
     */
    
    public void insertCart(Cart newCart) throws SQLException
    {
        /*Insert cart in database */

        String insert = "INSERT INTO Carts ( `User` ) ";
        insert += "VALUES ";
        insert += "( " + String.valueOf(newCart.getOwner()) + "); ";

        statement.executeUpdate(insert);

        String select = "SELECT id FROM Carts WHERE User = " + String.valueOf(newCart.getOwner()) + ";";
        ResultSet resultSet = statement.executeQuery(select);

        if (resultSet.next() == false)
        {
            return; //to do exception
        }
        int cartId = resultSet.getInt("id");

        /*Insert files that cart contains in the cart-files relation */

        for (int i = 0; i < newCart.getSize(); i++)
        {
            insert = "INSERT INTO Carts_has_Files ( `Carts_id`, `Files_id` ) ";
            insert += "VALUES ";
            insert += "(" + String.valueOf(cartId) + ", " + String.valueOf(newCart.getFile(i).getID()) + ");";
            statement.executeUpdate(insert);
        }

    }

    /**
     * Stores in connected database the keyword corellation with the target file given
     * @param file target file
     * @param keywords List of keywords that describe the file
     * @throws SQLException
     */

    public void insertKeywords ( File file, ArrayList<String> keywords) throws SQLException
    {
        for ( String keyword : keywords )
        {
            String insert = "INSERT INTO Keywords ( `Files_id`, `Keyword` ) " +
                            "VALUES " +
                            "(" + String.valueOf(file.getID()) + ", '" + keyword + "');";

            statement.executeUpdate(insert);
        }
    }

    /**
     * Returns the target user stored in the connected database
     *
     * @param username target user's username
     * @throws SQLException
     */

    public User getUser(String username) throws SQLException
    {
        String select = "SELECT * FROM Users WHERE Username = '" + username + "';";
        ResultSet resultSet = statement.executeQuery(select);

        if (resultSet.next() == false)
        {
            return null;
        }

        return new User(resultSet.getInt("id"), resultSet.getString("Username"), resultSet.getString("Password"),
                resultSet.getString("Email"), resultSet.getString("Surname"), resultSet.getString("Name"),
                resultSet.getInt("Credits"), resultSet.getInt("AvailableSpace"), resultSet.getString("RegistrationDate"), resultSet.getString("LastLogin"));
    }

    /**
     * Returns the cart that belongs to the target user
     *
     * @param userId target user's id
     * @throws SQLException
     */
    
    public Cart getUserCart(User user) throws SQLException
    {
        int userId = user.getId();
        String select = "SELECT id FROM Carts WHERE User = " + String.valueOf(userId) + ";";
        ResultSet resultSet = statement.executeQuery(select);

        /* empty result */

        if (resultSet.next() == false)
        {
            return null;
        }

        int cartId = resultSet.getInt("id");

        select = "SELECT * FROM Files, Carts_has_Files WHERE Carts_has_Files.Carts_id = "
                + String.valueOf(cartId) + " AND Files.id = Carts_has_Files.Files_id;";
        resultSet = statement.executeQuery(select);

        Cart cart = new Cart(cartId, userId);
        while (resultSet.next())
        {
            cart.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return cart;
    }

    /**
     * Returns from the connected database the file with the given id
     *
     * @param fileId file id to be returned
     * @throws SQLException
     */
    
    public File getFile(int fileId) throws SQLException
    {
        String select = "SELECT * FROM Files WHERE id = " + String.valueOf(fileId) + ";";
        ResultSet resultSet = statement.executeQuery(select);

        /* empty result */

        if (resultSet.next() == false) {
            return null;
        }

        return new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                resultSet.getString("Status"), resultSet.getInt("Value"),
                resultSet.getInt("Downloads"), resultSet.getString("Category"),
                resultSet.getString("Description"), resultSet.getString("DatePublished"));
    }

    public String getUsername(int userId) throws SQLException
    {
        String select = "SELECT Username From Users WHERE id = " + String.valueOf(userId);
        ResultSet resultSet = statement.executeQuery(select);

        if ( resultSet.next() == false )
        {
            return null;
        }

        return resultSet.getString("Username");
    }

    /**
     * Return the files that target users owns as an ArrayList
     *
     * @param userId target user
     * @throws SQLException
     */
    
    public ArrayList<File> getFilesOfUser(int userId) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();

        String select = "SELECT * FROM Files WHERE Owner = " + String.valueOf(userId) + ";";
        ResultSet resultSet = statement.executeQuery(select);

        while (resultSet.next())
        {
            files.add(new File(resultSet.getInt("id"), userId, resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    /**
     * Returns a list of files that are under the target keyword
     * @param keyword the target keyword
     */

    public ArrayList<File> getKeywordMathced( String keyword ) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files, Keywords WHERE Keyword ='" + keyword + "' AND Files_id = id;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    /**
     * Returns a list of files that are under the target keyword
     * @param keyword the target keyword
     */

    public ArrayList<File> getPublicKeywordMathced( String keyword ) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files, Keywords WHERE Status = 'Public' AND Keyword ='" + keyword + "' AND Files_id = id;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }


    /**
     * Returns the 15 Top Downloads
     *
     * @return list of the most popular files
     */

    public ArrayList<File> getMostPopularFiles() throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files ORDER BY Downloads DESC LIMIT 0,10;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    /**
     * Returns the 10 Top Downloads
     *
     * @return list of the most popular public files
     */

    public ArrayList<File> getMostPopularPublicFiles() throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files WHERE Status = 'Public' ORDER BY Downloads DESC LIMIT 0,10;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }


    /**
     * Returns a list that contains the newest files
     *
     * @throws SQLException
     */

    public ArrayList<File> getNewestFiles() throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files ORDER BY DatePublished DESC LIMIT 0, 5;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

        /**
     * Returns a list that contains the newest files
     *
     * @throws SQLException
     */

    public ArrayList<File> getNewestPublicFiles() throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        try
        {
        String select = "SELECT * FROM Files WHERE Status = 'Public' ORDER BY DatePublished DESC LIMIT 0, 5;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }
        }
        catch(Exception ex)
        {

        }
        return files;
    }


    /**
     * Returns a list of files that belong to the target category
     *
     * @param category target category
     * @return
     */

    public ArrayList<File> getByCategory( String category ) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files WHERE Category = " + category;
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    /**
     * Returns a list of files that have the target status
     * @param status target status
     * @return
     * @throws SQLException
     */

    public ArrayList<File> getByStatus ( String status ) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files WHERE Status = " + status;
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    public ArrayList<File> getByDirectory(String directory) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files;";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() )
        {
            File file = new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished"));

            if ( file.getFolder().equals(directory) )
            {
                files.add(file);
            }
        }

        return files;        
    }

    public ArrayList<File> getRangedFiles(int supper, int upper) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files LIMIT " + String.valueOf(supper) + ", " + String.valueOf(upper);
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    public ArrayList<File> getRangedPublicFiles(int supper, int upper) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files WHERE Status = 'Public' LIMIT " + String.valueOf(supper) + ", " + String.valueOf(upper);
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }


    public ArrayList<File> getFilesOfUserWithKeyword(int userId, String keyword ) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files, Keywords WHERE Keywords.Keyword ='" + keyword + "' AND Keywords.Files_id = Files.id AND Files.Owner = " + String.valueOf(userId) + ";";
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    public void removeFromCart( Cart cart, int item ) throws SQLException
    {
        String delete = "DELETE FROM Carts WHERE ";
        delete = "DELETE FROM Carts_has_Files WHERE Carts_id = " + String.valueOf(cart.getId()) + " AND Files_id = " + String.valueOf(item);
        statement.execute(delete);
    }

    public ArrayList<String> getUserDirectories ( User user ) throws SQLException
    {
        String select = "SELECT Name FROM Files WHERE Owner = " + String.valueOf( user.getId() ) + ";";
        ResultSet resultSet = statement.executeQuery(select);
        ArrayList<String> directories = new ArrayList();
        while ( resultSet.next() == true )
        {
            int endIndex = resultSet.getString("Name").indexOf("/");
            String directory = resultSet.getString("Name").substring(0, endIndex);
            if ( directory.isEmpty() == false && directories.contains( directory ) == false )
            {
                directories.add( directory );
            }
        }

        return directories;
    }

    public void AddToCart(Cart cart, int fileId) throws SQLException
    {
        String insert = "INSERT INTO Carts_has_Files ( `Carts_id`, `Files_id` ) ";
            insert += "VALUES ";
            insert += "(" + String.valueOf(cart.getId()) + ", " + String.valueOf(fileId) + ");";
        statement.executeUpdate(insert);
    }

    public void deleteUser(int userid) throws SQLException
    {
        String delete= "DELETE FROM Users WHERE id = " + String.valueOf(userid);
        statement.executeUpdate(delete);

        delete = "DELETE FROM Files WHERE owner = "  + String.valueOf(userid);
        statement.executeUpdate(delete);

        String select = "SELECT id FROM Carts WHERE User = " + String.valueOf(userid);
        ResultSet resultSet = statement.executeQuery(select);
        int cartId=-1;
        while ( resultSet.next() )
        {
            cartId = resultSet.getInt("id");
        }
        if (cartId != -1 )
        {
            delete = "DELETE FROM Carts WHERE owner = "  + String.valueOf(userid);
            statement.executeUpdate(delete);

            delete = "DELETE FROM Carts_has_Files WHERE Carts_id = " + String.valueOf(cartId);
            statement.executeUpdate(delete);
        }
    }

    public void changeMail ( User user, String newMail ) throws SQLException
    {
         String update = "UPDATE Users SET Email = '" + newMail + "' WHERE id = " + user.getId();
         statement.executeUpdate(update);
    }

    public void changePassword ( User user, String newPassword ) throws SQLException
    {
         String update = "UPDATE Users SET Password = '" + newPassword + "' WHERE id = " + user.getId();
         statement.executeUpdate(update);
    }

    public void chargeSpace(User user, float space) throws SQLException
    {
        String select = "SELECT AvailableSpace FROM Users WHERE id = " + String.valueOf( user.getId() );
        ResultSet resultSet = statement.executeQuery(select);
        resultSet.next();

        int available = resultSet.getInt("AvailableSpace");
                        //( Gb --> bytes )//
        available += space * 1024 * 1024 * 1024;
        String update = "UPDATE Users SET AvailableSpace = " + String.valueOf(available) + " WHERE id = " + String.valueOf( user.getId() );
        statement.executeUpdate(update);
    }

    public void chargeUser ( User user, float amount ) throws SQLException
    {
        String update = " UPDATE Users SET Credits = " + String.valueOf(user.getCredits() - amount) + " WHERE id = " + String.valueOf(user.id);
        statement.executeUpdate(update);
    }

    public void deleteCart(Cart cart) throws SQLException
    {
        String delete = "DELETE FROM Carts_has_files WHERE Carts_id = " + String.valueOf(cart.getId());
        statement.executeUpdate(delete);
    }

    public void payUser ( int userId, float amount ) throws SQLException
    {
        String select = "SELECT Credits FROM Users WHERE id = " + String.valueOf( userId );
        ResultSet resultSet = statement.executeQuery(select);

        resultSet.next();

        int credits = resultSet.getInt("Credits");
        credits += amount;
        String update = "UPDATE Users SET Credits = " + String.valueOf(credits) + " WHERE id = " + String.valueOf( userId );
        statement.executeUpdate(update);
    }

    public void giveSpace ( int userId, int space ) throws SQLException
    {
        String select = "SELECT AvailableSpace FROM Users WHERE id = " + String.valueOf( userId );
        ResultSet resultSet = statement.executeQuery(select);

        resultSet.next();

        int availableSpace  = resultSet.getInt("AvailableSpace");
        availableSpace += space;
        String update = "UPDATE Users SET AvailableSpace = " + String.valueOf(availableSpace) + " WHERE id = " + String.valueOf( userId );
        statement.executeUpdate(update);
    }

    public void setLastLogin ( User user, String date ) throws SQLException
    {
        String update = "UPDATE Users SET LastLogin = '" + date + "' WHERE id = " + String.valueOf(user.getId());
        statement.executeUpdate(update);
    }

    public void updateDownloadCounter ( int fileId ) throws SQLException
    {
        String select = "SELECT Downloads FROM Files WHERE id = " + String.valueOf( fileId );
        ResultSet resultSet = statement.executeQuery(select);

        resultSet.next();

        int downloads  = resultSet.getInt("Downloads");
        downloads ++;
        String update = "UPDATE Files SET Downloads = " + String.valueOf(downloads) + " WHERE id = " + String.valueOf( fileId );
        statement.executeUpdate(update);
    }

    public int getDownloadsCounter() throws SQLException
    {
        String select = "SELECT SUM(Downloads) FROM Files";
        ResultSet resultSet = statement.executeQuery(select);
        resultSet.next();
        return resultSet.getInt(1);
    }

    public void removeAvailableSpace(int userId, float space) throws SQLException
    {
        String select = "SELECT AvailableSpace FROM Users WHERE id = " + String.valueOf( userId );
        ResultSet resultSet = statement.executeQuery(select);

        resultSet.next();

        int availableSpace  = resultSet.getInt("AvailableSpace");
        availableSpace -= space;
        String update = "UPDATE Users SET AvailableSpace = " + String.valueOf(availableSpace) + " WHERE id = " + String.valueOf( userId );
        statement.executeUpdate(update);
    }

    public void updateFileCategory(int fileId, String category) throws SQLException
    {
        String update = "UPDATE Files SET Category = '" + category + "' WHERE id =  " + String.valueOf(fileId);
        statement.executeUpdate(update);
    }

    public void updateFileType(int fileId, String type) throws SQLException
    {
        String update = "UPDATE Files SET Type = '" + type    + "' WHERE id =  " + String.valueOf(fileId);
        statement.executeUpdate(update);
    }

    public void updateFileDescription(int fileId, String description) throws SQLException
    {
        String update = "UPDATE Files SET Description   = '" + description + "' WHERE id =  " + String.valueOf(fileId);
        statement.executeUpdate(update);
    }

    public void updateFileValue(int fileId, float value) throws SQLException
    {
        String update = "UPDATE Files SET Value = " + value + " WHERE id =  " + String.valueOf(fileId);
        statement.executeUpdate(update);
    }

    public void deleteFile ( int fileId ) throws SQLException
    {
        String update = "DELETE FROM Carts_has_Files WHERE Files_id = " + String.valueOf(fileId);
        statement.executeUpdate(update);
        update = "DELETE FROM Keywords WHERE Files_id = " + String.valueOf(fileId);
        statement.executeUpdate(update);
        update = "DELETE FROM Files WHERE id = " + String.valueOf(fileId);
        statement.executeUpdate(update);
    }

    public void renameDirectory(String oldName, String newName) throws SQLException
    {
        ArrayList<File> files = this.getByDirectory(oldName);

        for ( File file : files )
        {
            file.ChangeFolder(newName);
            String update = "UPDATE Files SET Name = '" + file.getFullName() + "' WHERE id = " + String.valueOf(file.getID());
            statement.executeUpdate(update);
        }
    }

    public ArrayList<File> getUserKeywordsMatched(String username, String keyword) throws SQLException
    {
        ArrayList<File> files = new ArrayList<File>();
        String select = "SELECT * FROM Files, Keywords WHERE Keyword ='" + keyword + "' AND Files_id = id AND Owner = " + String.valueOf(this.getUser(username).getId());
        ResultSet resultSet = statement.executeQuery(select);

        while ( resultSet.next() == true )
        {
            files.add(new File(resultSet.getInt("id"), resultSet.getInt("Owner"), resultSet.getString("Name"),
                    resultSet.getString("Status"), resultSet.getInt("Value"),
                    resultSet.getInt("Downloads"), resultSet.getString("Category"),
                    resultSet.getString("Description"), resultSet.getString("DatePublished")));
        }

        return files;
    }

    @Override
    protected void finalize() throws SQLException, Throwable
    {
        super.finalize();
        statement.close();
        connection.close();
    }
}
